package com.dwjm.exam.stu_list;

import com.dwjm.exam.pojo.Student;
import utils.ConnectionDatabaseForMySQLUseJdbc1;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    Connection connection = null;
    PreparedStatement pstmt = null;
    ResultSet rest = null;

    public StudentDaoImpl() {
        connection = ConnectionDatabaseForMySQLUseJdbc1.getConnection();
    }

    @Override
    public List<Student> List(String name, int page, int pageSize) {
        ArrayList<Student> list= new ArrayList<>();
        try {
            if(name!=null&&!name.isEmpty()){
                pstmt = connection.prepareStatement("select * from student where name like ? limit ?,?");
                pstmt.setString(1, "%"+name+"%");
                pstmt.setInt(2,(page-1)*pageSize);
                pstmt.setInt(3,pageSize);
            }else{
                pstmt = connection.prepareStatement("select *from student limit ?,?");
                pstmt.setInt(1,(page-1)*pageSize);
                pstmt.setInt(2,pageSize);
            }
            rest = pstmt.executeQuery();
            Student student = null;
            while(rest.next()){
                student = new Student();
                student.setStudentId(rest.getString("studentId"));
                student.setName(rest.getString("name"));
                student.setGender(rest.getString("gender"));
                student.setBirthDate(rest.getDate("birthDate"));
                student.setIdNumber(rest.getString("idNumber"));
                student.setCollege(rest.getString("college"));
                student.setMajor(rest.getString("major"));
                student.setClassName(rest.getString("className"));
                student.setStatus(rest.getString("status"));
                student.setDormId(rest.getString("dormId"));
                student.setBedNumber(rest.getString("bedNumber"));
                student.setPhone(rest.getString("phone"));
                student.setEmail(rest.getString("email"));
                student.setEmergencyContact(rest.getString("emergencyContact"));
                student.setUserName(rest.getString("username"));
                student.setPhone(rest.getString("password"));
                list.add((student));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int count(String name) throws SQLException {
        if (name!=null&&!name.isEmpty()){
            pstmt = connection.prepareStatement("select count(*) as total from student where name=?");
            pstmt.setString(1,name);
        }else{
            pstmt = connection.prepareStatement("select count(*) as total from student");
        }

        try {
            System.out.println("执行查询商品总数SQL: " + pstmt.toString());

            if (connection == null) {
                System.out.println("数据库连接为空，无法执行查询");
                return 0;
            }

            rest = pstmt.executeQuery();

            if (rest.next()) {
                int total = rest.getInt("total");
                System.out.println("商品总数: " + total);
                return total;
            }
        } catch (SQLException e) {
            System.out.println("查询商品总数时SQL异常: " + e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("查询商品总数时发生其他异常: " + e.getMessage());
            e.printStackTrace();
        }
        return 0;
    }
    @Override
    public boolean deleteStudent(String studentId) {
        String sql = "update  student set status='离校' where studentId=?";
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, studentId);
            int i = pstmt.executeUpdate();
          if (i > 0) {
              return true;
          }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public int deleteMultipleStudents(String[] studentIds) {
        // If no student IDs are provided, return 0 (no students deleted)
        if (studentIds == null || studentIds.length == 0) {
            return 0;
        }

        // Create SQL with placeholders for each student ID
        StringBuilder sqlBuilder = new StringBuilder("UPDATE student SET status='离校' WHERE studentId IN (");
        for (int i = 0; i < studentIds.length; i++) {
            sqlBuilder.append("?");
            if (i < studentIds.length - 1) {
                sqlBuilder.append(",");
            }
        }
        sqlBuilder.append(")");

        int deletedCount = 0;
        try {
            pstmt = connection.prepareStatement(sqlBuilder.toString());

            // Set parameters for each student ID
            for (int i = 0; i < studentIds.length; i++) {
                pstmt.setString(i + 1, studentIds[i]);
            }

            deletedCount = pstmt.executeUpdate();
            return deletedCount;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return deletedCount;
    }
    @Override
    public List<Student> getStudentById(String studentId) {
        ArrayList<Student> list = new ArrayList<>();
        try {
            // 准备SQL查询语句，根据学生ID查询
            pstmt = connection.prepareStatement("SELECT * FROM student WHERE studentId = ?");
            pstmt.setString(1, studentId);

            // 执行查询并处理结果
            rest = pstmt.executeQuery();

            while(rest.next()) {
                Student student = new Student();
                // 设置学生属性...
                list.add(student);
            }
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源...
        }
        return list;
    }

}